<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="<#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.dao.${IDaoName}">

    <!--添加-->
	<insert id="add" parameterType="<#if !entityName??>java.util.HashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>">
		<#-- 如果数据库有主键并且是递增，则传过来的list没有主键 -->
		<#if dataBaseType == "mysql">
		insert into `${realTableName}`
		</#if>
		<#if dataBaseType == "oracle" || dataBaseType == "sqlserver" || dataBaseType == "postgresql">
		insert into ${realTableName}
		</#if>
        <trim prefix="(" suffix=")" suffixOverrides=",">
        <#list columnList as data>
            <if test="${data.sqlParamColumnEng} != null">
                ${data.columnsEng},
            </if>
        </#list>
		</trim>
		<trim prefix="values (" suffix=")" suffixOverrides=",">
			<#list columnList as data>
				<if test="${data.sqlParamColumnEng} != null">
					<#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>,
				</if>
			</#list>
		</trim>
	</insert>

    <!--删除-->
	<delete id="delete" parameterType="<#if !entityName??>java.util.HashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>">
		<#if dataBaseType == "mysql">
		delete from `${realTableName}`
		</#if>
        <#if dataBaseType == "oracle" || dataBaseType == "sqlserver" || dataBaseType == "postgresql">
		delete from ${realTableName}
		</#if>
		<where>
		<#list primaryKeyModelList as primaryKey>
            <if test="${primaryKey.sqlParamColumnEng} != null">
                and ${primaryKey.columnsEng}=<#noparse>#{</#noparse>${primaryKey.sqlParamColumnEng}<#noparse>}</#noparse>
            </if>
            <if test="${primaryKey.sqlParamColumnEng} == null">
                and 1 = 0
            </if>
		</#list>
		</where>
	</delete>

    <!--更新-->
	<update id="update" parameterType="<#if !entityName??>java.util.HashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>">
		<#if dataBaseType == "mysql">
		update `${realTableName}`
		</#if>
        <#if dataBaseType == "oracle" || dataBaseType == "sqlserver" || dataBaseType == "postgresql">
		update ${realTableName}
		</#if>
		<trim prefix="set" suffixOverrides=",">
			<#list columnList as data>
				<if test="${data.sqlParamColumnEng} != null">
					${data.columnsEng}=<#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>,
				</if>
			</#list>
		</trim>
		<where>
		<#list primaryKeyModelList as primaryKey>
            <if test="${primaryKey.sqlParamColumnEng} != null">
                and ${primaryKey.columnsEng}=<#noparse>#{</#noparse>${primaryKey.sqlParamColumnEng}<#noparse>}</#noparse>
            </if>
            <if test="${primaryKey.sqlParamColumnEng} == null">
                and 1 = 0
            </if>
		</#list>
		</where>
	</update>

    <!--固定条件查询-->
	<select id="select" parameterType="<#if !entityName??>java.util.HashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>"
		resultType="<#if !entityName??>java.util.LinkedHashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>">
		<#if dataBaseType == "mysql" || dataBaseType == "postgresql" || dataBaseType == "sqlserver">
			select
			<#list selectColumnListMapper as data>
				${data.columnsEng} as "${data.sqlParamColumnEng}"<#if data_has_next>,</#if>
			</#list>
            <#if dataBaseType == "mysql">
			from `${realTableName}`
			</#if>
            <#if dataBaseType == "postgresql" || dataBaseType == "sqlserver">
            from ${realTableName}
            </#if>
			<where>
				<#list columnList as data>
					<if test="${data.sqlParamColumnEng} != null">
						and ${data.columnsEng}=<#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>
					</if>
				</#list>
			</where>
        <#if dataBaseType == "sqlserver">
            <if test="orderStr == '' or orderStr == null">
                order by <#list primaryKeyModelList as primaryKey>${primaryKey.columnsEng}<#if primaryKey_has_next>,</#if></#list>
            </if>
        </#if>
			<if test="orderStr != '' and orderStr != null">
				order by <#noparse>${</#noparse>orderStr<#noparse>}</#noparse>
			</if>
        <#if dataBaseType == "mysql">
            <if test="start != null and pageSize != null">
                limit <#noparse>#{</#noparse>start<#noparse>}</#noparse>,<#noparse>#{</#noparse>pageSize<#noparse>}</#noparse>
            </if>
        </#if>
        <#if dataBaseType == "postgresql">
            <if test="start != null and pageSize != null">
                limit <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> offset <#noparse>#{</#noparse>start<#noparse>}</#noparse>
            </if>
        </#if>
        <#if dataBaseType == "sqlserver">
            <if test="start != null and pageSize != null">
                offset <#noparse>#{</#noparse>start<#noparse>}</#noparse> rows fetch next <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> rows only
            </if>
        </#if>
		</#if>

		<#if dataBaseType == "oracle">
			SELECT
			<#list selectColumnListMapper as data>
				${data.columnsEng} as "${data.sqlParamColumnEng}"<#if data_has_next>,</#if>
			</#list>
			<if test="startIndex == null or endIndex == null">
				FROM ${realTableName}
	              <where>
					<#list columnList as data>
						<if test="${data.sqlParamColumnEng} != null">
							and ${data.columnsEng}=<#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>
						</if>
					</#list>
				  </where>
				  <if test="orderStr != '' and orderStr != null">
					order by <#noparse>${</#noparse>orderStr<#noparse>}</#noparse>
				  </if>
			</if>
			<if test="startIndex != null and endIndex != null">

	  			FROM (SELECT t2.*, ROWNUM rowno

	          	FROM (SELECT t1.*

	                  FROM ${realTableName} t1

	                  <where>

						<#list columnList as data>
							<if test="${data.sqlParamColumnEng} != null">
								and ${data.columnsEng}=<#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>
							</if>
						</#list>

					  </where>

	                  <if test="orderStr != '' and orderStr != null">
						order by <#noparse>${</#noparse>orderStr<#noparse>}</#noparse>
					  </if>

	                  ) t2

	           <![CDATA[WHERE ROWNUM <= <#noparse>#{</#noparse>endIndex<#noparse>}</#noparse>) t3

	 		   WHERE t3.rowno > <#noparse>#{</#noparse>startIndex<#noparse>}</#noparse>]]>
	 		</if>
		</#if>

	</select>

    <!--分页（模糊查询的公共条件）-->
    <sql id="likeSelectConditions">
        <#list queryColumnList as data>
            <#if data.compareValue==">= && <=">
                <if test="start${data.sqlParamColumnEng?cap_first} != null <#if data.serviceType != "日期">and start${data.sqlParamColumnEng?cap_first} != ''</#if> ">
                    and ${data.columnsEng} <![CDATA[>=]]> <#noparse>#{</#noparse>start${data.sqlParamColumnEng?cap_first}<#noparse>}</#noparse>
                </if>
                <if test="end${data.sqlParamColumnEng?cap_first} != null <#if data.serviceType != "日期">and end${data.sqlParamColumnEng?cap_first} != ''</#if> ">
                    and ${data.columnsEng} <![CDATA[<=]]> <#noparse>#{</#noparse>end${data.sqlParamColumnEng?cap_first}<#noparse>}</#noparse>
                </if>
            <#else>
                <if test="${data.sqlParamColumnEng} != null <#if data.serviceType != "日期">and ${data.sqlParamColumnEng} != ''</#if> ">
                <#if data.compareValue=="like">
                    <#if dataBaseType == "mysql">
                    and ${data.columnsEng} like "%"<#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>"%"
                    </#if>
                    <#if dataBaseType == "oracle" || dataBaseType == "postgresql">
                    and ${data.columnsEng} like <#noparse>'%' || #{</#noparse>${data.sqlParamColumnEng}<#noparse>} || '%'</#noparse>
                    </#if>
                    <#if dataBaseType == "sqlserver">
                    and ${data.columnsEng} like <#noparse>'%' + #{</#noparse>${data.sqlParamColumnEng}<#noparse>} + '%'</#noparse>
                    </#if>
                <#else>
                and ${data.columnsEng} <![CDATA[${data.compareValue}]]> <#noparse>#{</#noparse>${data.sqlParamColumnEng}<#noparse>}</#noparse>
                </#if>
                </if>
            </#if>
        </#list>
    </sql>

    <!--分页（模糊）查询-->
	<select id="likeSelect" parameterType="<#if !entityName??>java.util.HashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>"
		resultType="<#if !entityName??>java.util.LinkedHashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>">
		<#if dataBaseType == "mysql" || dataBaseType == "postgresql" || dataBaseType == "sqlserver">
			select
			<#list selectColumnListMapper as data>
				${data.columnsEng} as "${data.sqlParamColumnEng}"<#if data_has_next>,</#if>
			</#list>

            <#if dataBaseType == "mysql">
                from `${realTableName}`
            </#if>
            <#if dataBaseType == "postgresql" || dataBaseType == "sqlserver">
                from ${realTableName}
            </#if>

			<where>
                <include refid="likeSelectConditions"/>
			</where>
        <#if dataBaseType == "sqlserver">
            <if test="orderStr == '' or orderStr == null">
                order by <#list primaryKeyModelList as primaryKey>${primaryKey.columnsEng}<#if primaryKey_has_next>,</#if></#list>
            </if>
        </#if>
			<if test="orderStr != '' and orderStr != null">
				order by <#noparse>${</#noparse>orderStr<#noparse>}</#noparse>
			</if>
            <#if dataBaseType == "mysql">
            <if test="start != null and pageSize != null">
                limit <#noparse>#{</#noparse>start<#noparse>}</#noparse>,<#noparse>#{</#noparse>pageSize<#noparse>}</#noparse>
            </if>
            </#if>
            <#if dataBaseType == "postgresql">
            <if test="start != null and pageSize != null">
                limit <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> offset <#noparse>#{</#noparse>start<#noparse>}</#noparse>
            </if>
            </#if>
            <#if dataBaseType == "sqlserver">
            <if test="start != null and pageSize != null">
                offset <#noparse>#{</#noparse>start<#noparse>}</#noparse> rows fetch next <#noparse>#{</#noparse>pageSize<#noparse>}</#noparse> rows only
            </if>
            </#if>
		</#if>

		<#if dataBaseType == "oracle">
			SELECT
			<#list selectColumnListMapper as data>
				${data.columnsEng} as "${data.sqlParamColumnEng}"<#if data_has_next>,</#if>
			</#list>
			<if test="startIndex == null or endIndex == null">
				FROM ${realTableName}
	                  <where>
                          <include refid="likeSelectConditions"/>
					  </where>
					  <if test="orderStr != '' and orderStr != null">
						order by <#noparse>${</#noparse>orderStr<#noparse>}</#noparse>
					  </if>
			</if>
			<if test="startIndex != null and endIndex != null">
	  			FROM (SELECT t2.*, ROWNUM rowno

	          	FROM (SELECT t1.*

	                  FROM ${realTableName} t1

	                  <where>
                          <include refid="likeSelectConditions"/>
					  </where>

	                  <if test="orderStr != '' and orderStr != null">
						order by <#noparse>${</#noparse>orderStr<#noparse>}</#noparse>
					  </if>

	                  ) t2

	           <![CDATA[WHERE ROWNUM <= <#noparse>#{</#noparse>endIndex<#noparse>}</#noparse>) t3

	 		   WHERE t3.rowno > <#noparse>#{</#noparse>startIndex<#noparse>}</#noparse>]]>
 		   </if>
		</#if>

	</select>

    <!--分页（模糊）查询条数-->
	<select id="likeSelectCount" parameterType="<#if !entityName??>java.util.HashMap<#else><#if isCloudModel>${cloudSysEngName}<#else>${packageName}</#if>.entity.${entityName}</#if>"
		resultType="java.lang.Long">
		<#if dataBaseType == "mysql">
		select
			count(1)
		from `${realTableName}`
		</#if>
        <#if dataBaseType == "postgresql" || dataBaseType == "sqlserver">
        select
            count(1)
        from ${realTableName}
        </#if>
		<#if dataBaseType == "oracle">
		select
			count(1)
		from ${realTableName}
		</#if>

		<where>
            <include refid="likeSelectConditions"/>
		</where>
	</select>

</mapper>
